
<?php
require_once("connection.php");


function createOrderTable(){
	$sql = "CREATE TABLE IF NOT EXISTS `bookingorder` (
				`oid` int(11) NOT NULL AUTO_INCREMENT,
				`recName` varchar(50) NOT NULL,
				`recAddress` varchar(128) NOT NULL,
				`recPhoneNo` char(8) NOT NULL,
				`deliverDate` date DEFAULT NULL,
				`orderDate` datetime DEFAULT CURRENT_TIMESTAMP,
				`cid` int NOT NULL,
				PRIMARY KEY (`oid`)
		)";
}

function insertOrderTuple(){
	$cid = clean($_POST['cid']);
	$reName = clean($_POST['reName']);
	$reAddress = clean($_POST['reAddress']);
	$rePhoneNo = clean($_POST['rePhoneNo']);
	$deliverDate = clean($_POST['deliverDate']);
	$sql_insertTobookingorder = "INSERT INTO bookingorder(cid, recName, recAddress, recPhoneNo, deliverDate)
		  VALUES('$cid','$reName','$reAddress','$rePhoneNo','$deliverDate')";
	$fname = $_POST['fname'];
	$fsize = clean($_POST['fsize']);
	$fnum = clean($_POST['num']);
	$sql_available = "SELECT f.quantity 
					  FROM flowers f
					  WHERE f.fname = '$fname' AND f.size = '$fsize'";
	$favaliableNumResult = mysql_query($sql_available);
	if (!$favaliableNumResult){
		die('Error: ' . mysql_error());
		return false;
	}
	$row_num = mysql_fetch_row($favaliableNumResult);
	$avaliable_num = $row_num[0];
	if ($fnum > $avaliable_num ){
	    echo ("<SCRIPT LANGUAGE='JavaScript'>
        window.alert('Delete Succesfully!');
       </SCRIPT>");

		exit();
		return false;
	}
	if (!mysql_query($sql_insertTobookingorder)){
		die('Error: ' . mysql_error());
		return false;
	}else{
		$sql_getOid = "SELECT LAST_INSERT_ID()";
		$oidResult = mysql_query($sql_getOid);
		if ($oidResult){
				$row = mysql_fetch_row($oidResult);
				$oid = $row[0];
				$sql_insertToContains = "INSERT INTO contains(oid,fname,fsize,num)
							 VALUES('$oid','$fname','$fsize','$fnum')";
				if (!mysql_query($sql_insertToContains)){
					die('Error: '.mysql_error());
					return false;
				}else{
					$sql_updateFlowerNumber = "UPDATE flowers
											   SET flowers.quantity = flowers.quantity - '$fnum'
											   WHERE flowers.fname = '$fname' AND flowers.size = '$fsize'";
					if (!mysql_query($sql_updateFlowerNumber)){
						die('Error: '.mysql_error());
						return true;
					}else{
						return false;
					}
				}
		}else{
			die('Error: '.mysql_error());
			return false;
		}
	}
}

function clean($str) {
		$str = @trim($str);
		if(get_magic_quotes_gpc()) {
			$str = stripslashes($str);
		}
		return mysql_real_escape_string($str);
}
function modifyOrderTuple(){
	$oid = clean($_POST['oid']);
	$new_reName = clean($_POST['new_recName']);
	$new_reAddress = clean($_POST['new_recAddress']);
	$new_rePhoneNo = clean($_POST['new_recPhoneNo']);
	$new_deliverDate = clean($_POST['new_deliverDate']);
	$sql = "UPDATE bookingorder
			SET bookingorder.recName = '$new_reName', bookingorder.recAddress = '$new_reAddress', bookingorder.recPhoneNo = '$new_rePhoneNo', bookingorder.deliverDate = '$new_deliverDate' 
			WHERE bookingorder.oid = '$oid'";
	if (!mysql_query($sql)){
		die('Error: '.mysql_error());
	}
	$new_quantity = clean($_POST['new_num']);
	$sql_updateFlowers = "UPDATE flowers f, contains c
						  SET f.quantity = f.quantity - '$new_quantity' + c.num
						  WHERE c.fname = f.fname
						  AND c. fsize = f.size
						  AND c.oid = '$oid'";
	if (!mysql_query($sql_updateFlowers)){
		die('Error: '.mysql_error());
	}
	$sql_change_num = "UPDATE contains
					   SET num = '$new_quantity'
					   WHERE contains.oid = '$oid'";
	if (!mysql_query($sql_change_num)){
		die('Error: '.mysql_error());
	}
}

function deleteOrderTuple(){
	$oid = $_POST['oid'];
	$sql_flowers = "UPDATE flowers f, contains c, bookingorder b
					SET f.quantity = f.quantity + c.num
					WHERE f.fname = c.fname
					AND f.size = c.fsize
					AND c.oid = '$oid'
					AND b.oid = '$oid'
					AND b.status = 'Processing'";

	
	if (!mysql_query($sql_flowers)){
		die('Error: '.mysql_error());
	}
	$sql = "DELETE FROM bookingorder 
			WHERE bookingorder.oid = '$oid'";
	if (!mysql_query($sql)){
		die('Error: '.mysql_error());
	}
	$sql_contains = "DELETE FROM contains 
			WHERE contains.oid = '$oid'";
	if (!mysql_query($sql_contains)){
		die('Error: '.mysql_error());
	}
}
/*-----------------------Start to execute from this point------------------------------*/

switch($_POST['method'])
{
	case 'insertOrder':
		insertOrderTuple();
		unset($_POST);
		header("location: cus_Myorder.php");
		break;
	case 'deleteOrder':
		deleteOrderTuple();
		header("location: cus_Myorder.php");
		unset($_POST);
		break;
	case 'modifyOrder':
	    modifyOrderTuple();
		unset($_POST);
		header("location: cus_Myorder.php");
		break;
	DEFAULT:
		echo 'error';
}

?>
